R Notebook

library(plotly)

Registered S3 method overwritten by 'data.table':
  method           from
  print.data.table     

Attaching package: ‘plotly’

The following object is masked from ‘package:ggplot2’:

    last_plot

The following object is masked from ‘package:stats’:

    filter

The following object is masked from ‘package:graphics’:

    layout

beds <- read_csv("raw_data/non_covid_raw_data/beds_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

Warning: One or more parsing issues, see `problems()` for detailsRows: 30458 Columns: 20── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (10): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, Specialty, SpecialtyQF, SpecialtyName, SpecialtyNameQF
dbl  (5): AllStaffedBeddays, TotalOccupiedBeddays, AverageAvailableStaffedBeds, AverageOccupiedBeds, PercentageOccupancy
lgl  (5): AllStaffedBeddaysQF, TotalOccupiedBeddaysQF, AverageAvailableStaffedBedsQF, AverageOccupiedBedsQF, PercentageOcc...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

beds %>% 
  filter(specialty_name == "All Acute") %>% 
  ggplot(aes(x = quarter, y = percentage_occupancy))+
  geom_line(aes(colour = hb), group = 1)+
  facet_wrap(~ hb)

---

library(lubridate)

Attaching package: ‘lubridate’

The following objects are masked from ‘package:base’:

    date, intersect, setdiff, union

library(zoo)

# change quarter column into the date at the start of each quarter
 age_sex <-  age_sex %>% 
    mutate(quarter = yq(quarter))

 # shows the total length of stay by age bracket for emergency inpatients
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

 # shows the mean length of stay by age bracket for elective inpatients
# can facet by sex 
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_col(aes(fill = age), position = "dodge")+
  theme(axis.text.x = element_text(angle = 45))+
  scale_x_date(breaks = 4)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.Error: Invalid input: date_trans works with objects of class Date only

age_sex %>% 
  mutate(date = yq(quarter),
         year = year(date),
         month = month(date, label = TRUE, abbr = FALSE),
         season = case_when(
           str_detect(month, "December") ~ "Winter",
           str_detect(month, "January") ~ "Winter",
           str_detect(month, "February") ~ "Winter",
           str_detect(month, "March") ~ "Spring",
           str_detect(month, "April") ~ "Spring",
           str_detect(month, "May") ~ "Spring",
           str_detect(month, "June") ~ "Summer",
           str_detect(month, "July") ~ "Summer",
           str_detect(month, "August") ~ "Summer",
           str_detect(month, "September") ~ "Autumn",
           str_detect(month, "October") ~ "Autumn",
           str_detect(month, "November") ~ "Autumn"),
         season = factor(season, order = TRUE)) 

Warning: All formats failed to parse. No formats found.

age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, sex) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = sex, group = sex))+
  theme(axis.text.x = element_text(angle = 45))+
  geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = sex), size = 0.5)+
  labs(title = "Emergency Inpatient by gender and average length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, sex) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = sex, group = sex))+
  theme(axis.text.x = element_text(angle = 45))+
  geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = sex), size = 0.5)+
  labs(title = "Elective Inpatient by gender and average length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg length of stay
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# elective inpatient by age and avg length of stay
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Elective inpatient by age and avg length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg episodes
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg episodes")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg episodes
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg episodes")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
  filter(admission_type %in% c("Emergency Inpatients", "Elective Inpatients")) %>% 
  group_by(quarter, admission_type) %>% 
  summarise(avg_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay, colour = admission_type))+
  geom_line(aes(group = admission_type))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

simd <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_and_simd.csv") %>% janitor::clean_names()

Rows: 40821 Columns: 18── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (11): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, AdmissionType, AdmissionTypeQF, SIMDQF, AverageLengthOfEpisodeQF, AverageLengthO...
dbl  (7): SIMD, Episodes, LengthOfEpisode, AverageLengthOfEpisode, Stays, LengthOfStay, AverageLengthOfStay
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#total episodes(hospitalisations?) by simd value
simd %>% 
  drop_na(simd) %>%
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(total_episodes = sum(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = total_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# plot avg stay length for most and least deprived
simd %>% 
  filter(simd %in% c(1,5)) %>% 
  drop_na(simd) %>% 
  group_by(quarter,simd) %>% 
  summarise(avg_stay_length = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay_length)) +
  geom_line(aes(colour = simd, group = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

speciality <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

Rows: 95270 Columns: 18── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (12): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, AdmissionType, AdmissionTypeQF, Specialty, SpecialtyName, AverageLengthOfEpisode...
dbl  (6): Episodes, LengthOfEpisode, AverageLengthOfEpisode, Spells, LengthOfSpell, AverageLengthOfSpell
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

ae_wait_times <- read_csv("raw_data/non_covid_raw_data/monthly_ae_waitingtimes_202206.csv") %>% janitor::clean_names()

glimpse(ae_wait_times)

#make a date and year column with the first date of every month
ae_wait_times <- ae_wait_times %>% 
  mutate(date = ym(month), .after = month,
         year = year(date))

#make a percent column with percent of patients meeting the 4hr target time
ae_wait_times <- ae_wait_times %>% 
  mutate(percent_4hr_target_achieved = (number_meeting_target_aggregate/number_of_attendances_aggregate)*100) %>% 
  #add an 8hr one - not currently used
mutate(percent_seen_within_8hr = ((number_of_attendances_aggregate-attendance_greater8hrs)/number_of_attendances_aggregate)*100)

ggplotly(for_plotly)

`geom_smooth()` using method = 'loess' and formula 'y ~ x'
Warning: `gather_()` was deprecated in tidyr 1.2.0.
Please use `gather()` instead.

target_2018 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2018) %>% 
  rename(ae_target_2018 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2018)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.